﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace _18.database
{
    class Program
    {
        //链接字符串中必须指定Charset和SslMode - 特别是utf8和none的时候。
        static void Main(string[] args)
        {
            //1. 基础
            //Insert();
            //Update();
            //Delete();
            //Query();

            //2. SqlHelper
            SqlHelper.ConnectionString = "server=127.0.0.1;port=3306;database=csharp;user=root;password=;Charset=utf8;SslMode=none;";
            int r = SqlHelper.ExecuteNonQuery(@"insert into User (Name,Age,Sex) values('鱼玄机',18,0),('柳如是',13,0),('林青霞',18,0)");
            Console.WriteLine("执行成功，影响了 {0} 行！", r);

            var res = SqlHelper.ExecuteQuery(@"select * from User");
            foreach (var item in res)
            {
                foreach (var f in item)
                {
                    Console.Write("{0,10}", f);
                }
                Console.WriteLine();
            }


            Console.ReadKey();
        }

        static void Insert()
        {
            //MySQL案例
            string connStr = "server=127.0.0.1;port=3306;database=csharp;user=root;password=;Charset=utf8;SslMode=none;";
            using (var conn = new MySqlConnection(connStr))
            {
                string sql = "insert into User (Name,Age,Sex) values('东承灵',18,0),('乔木依',13,0),('白素贞',1800,0)";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    conn.Open();
                    //int r = (int)cmd.ExecuteScalar();
                    int r = cmd.ExecuteNonQuery();
                    Console.WriteLine("插入了 {0} 条数据！", r);
                }
            }
        }
        static void Update()
        {
            //MySQL案例
            string connStr = "server=127.0.0.1;port=3306;database=csharp;user=root;password=;Charset=utf8;SslMode=none;";
            using (var conn = new MySqlConnection(connStr))
            {
                string sql = "update User set Name='月采屏' where Name='马齿苋'";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    conn.Open();
                    //int r = (int)cmd.ExecuteScalar();
                    int r = cmd.ExecuteNonQuery();
                    Console.WriteLine("更新了 {0} 条数据！", r);
                }
            }
        }
        static void Delete()
        {
            //MySQL案例
            string connStr = "server=127.0.0.1;port=3306;database=csharp;user=root;password=;Charset=utf8;SslMode=none;";
            using (var conn = new MySqlConnection(connStr))
            {
                string sql = "delete from User where Name='任我行'";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    conn.Open();
                    //int r = (int)cmd.ExecuteScalar();
                    int r = cmd.ExecuteNonQuery();
                    Console.WriteLine("删除了 {0} 条数据！", r);
                }
            }
        }
        static void Query()
        {
            //MySQL案例
            string connStr = "server=127.0.0.1;port=3306;database=csharp;user=root;password=;Charset=utf8;SslMode=none;";
            //using (var conn = new SqlConnection(connStr))
            using (var conn = new MySqlConnection(connStr))
            {
                string sql = "select * from User";
                //using (var cmd = new SqlCommand(sql, conn))
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                Console.Write("{1,10}:{0,15}|", reader.GetValue(i), reader.GetName(i));
                            }
                            Console.WriteLine();
                        }
                    }
                }
            }
        }
    }
    static class SqlHelper
    {
        public static string ConnectionString { get; set; }
        private static MySqlConnection conn;
        private static MySqlConnection Connection
        {
            get
            {
                if (ConnectionString == null)
                {
                    throw new ArgumentNullException("链接字符串为空！请先设置链接字符串");
                }
                lock (ConnectionString)
                {
                    if (conn != null)
                    {
                        return conn;
                    }
                    conn = new MySqlConnection(ConnectionString);
                    return conn;
                }
            }
        }


        public static int ExecuteNonQuery(string sql)
        {
            using (var c = Connection)
            {
                using (var cmd = new MySqlCommand(sql, c))
                {
                    conn.Open();
                    int r = cmd.ExecuteNonQuery();
                    return r;
                }
            }
        }

        public static List<object[]> ExecuteQuery(string sql)
        {
            using (var c = Connection)
            {
                using (var cmd = new MySqlCommand(sql, c))
                {
                    conn.Open();
                    var reader = cmd.ExecuteReader();
                    List<object[]> list = new List<object[]>();
                    while (reader.Read())
                    {
                        //reader.
                        object[] os = new object[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            os[i] = reader.GetValue(i);
                        }
                        list.Add(os);
                    }
                    return list;
                }
            }
        }
    }
}
